

* distribution of EDUCATION GROUPS  
qui{
clear
use "${data}/MEPS_merged_Health_Whites.dta"
keep if AGE1<65 & AGE1>24
keep if INSCOP_Y1==1 // this covers R1-R3 which we are using here.
tab educ_group [aw=Sample_Weight]
collapse (count) ID [aw=Sample_Weight], by (educ_group)
egen total= sum(ID)
replace ID=ID/total
drop total
sort educ_group
drop educ_group
outsheet using "${data_model}\Educ_dist.txt", nolabel nonames replace	
}


* O probabilities - transitions - targets  
* Tables 57 & 58 
qui{
clear
use "${data}/MEPS_merged_Health_Whites.dta"
set more off
keep if AGE_YR1<65 & AGE_YR1>24

keep if INSCOP_Y1==1 & INSCOP_Y2==1  // use R1 and R3.
drop if labor_force_Y1==. | labor_force_Y2==. 

keep if AGE_YR1>=30 & AGE_YR1<=55


*** transitions of insurance
gen ESHI_Y1=0 if labor_force_Y1!=3 & labor_force_Y1!=5
gen ESHI_Y2=0 if labor_force_Y2!=3 & labor_force_Y2!=5
replace ESHI_Y1=1 if ESHI_Y1==.
replace ESHI_Y2=1 if ESHI_Y2==.

gen Lagged_ESHI = 1 if (labor_force_Y2==4 | labor_force_Y2==5) & labor_force_Y1!=3 & labor_force_Y1!=5 // No ESHI last yr, but FT this year
replace Lagged_ESHI = 2 if (labor_force_Y2==4 | labor_force_Y2==5) & (labor_force_Y1==3 | labor_force_Y1==5 ) // eshi last year and ft this year

gen Lagged_ESHI_new = 1 if  labor_force_Y1!=3 & labor_force_Y1!=5 
replace Lagged_ESHI_new = 2 if  (labor_force_Y1==3 | labor_force_Y1==5 ) 


label define ESHI_last 1 "No ESHI last yr, FT currently" 2 "ESHI last yr, FT currently"
label values Lagged_ESHI ESHI_last
label var Lagged_ESHI "Lagged ESHI and Current Emp"


label var EMPLOYMENT_YR2_v1 "Employed FT"

tab EMP_Y1
replace EMP_Y1=1 if EMP_Y1==0

label define EMP_last 1 "Not Employed or PT" 2 "Full Time"
label values EMP_Y1 EMP_last
label var EMP_Y1 "Lagged Employment"


* Table 57 - left panel 
table ( EMP_Y1) (educ_group),  statistic(mean EMPLOYMENT_YR2_v1) nototals nformat(%5.2f) 
collect title "Fraction Employed FT, ages 30-55, MEPS"
collect export "${out_tables}/Target_offers_2.tex", tableonly replace


* Table 58 - left panel
table ( Lagged_ESHI_new) (educ_group) ,  statistic(mean ESHI_Y2) nototals nformat(%5.2f) 
collect title "Fraction with ESHI, ages 30-55, MEPS"
collect export "${out_tables}/Target_offers_1_new.tex", tableonly replace


}

 
* Wage stats for calibration 

*1. wages by age groups
* Table 63 - left panel 

qui{
clear
use "${data}/MEPS_merged_Health_Whites.dta"
set more off
keep if AGE_YR1<65 & AGE_YR1>24
keep if INSCOP_Y1==1  // use R1 and R3.
keep if  EMPLOYMENT_YR1_v1 == 1   // KEEP FT in yr1 
keep if H1==3

drop age_group*

gen age_group=1 if AGE_YR1>=25 & AGE_YR1<30
replace age_group=2 if AGE_YR1>=35 & AGE_YR1<40
replace age_group=3 if AGE_YR1>=45 & AGE_YR1<50
replace age_group=4 if AGE_YR1>=55 & AGE_YR1<60
drop if age_group==.
//collapse (mean) HRWG1, by(educ_group age_group)
label var age_group "Age Group"
label define lbl_age 1 "Ages 25-29" 2 "Ages 35-39" 3 "Ages 45-49" 4 "Ages 55-59"
label values age_group lbl_age
tabstat HRWG1, stat(min p1 p5 p10 p95 p99 max)

table ( age_group) (educ_group),  statistic(mean HRWG1) nototals nformat(%5.1f) 
collect title "Average Wages, FT Workers in Good H, MEPS"
collect export "${out_tables}/Target_av_wages.tex", tableonly replace
}
*
*2. wages by H 
* Table 64 - left panel 
qui{
clear
use "${data}/MEPS_merged_Health_Whites.dta"
set more off
drop if AGE_YR1<40 & AGE_YR1>50
keep if INSCOP_Y1==1  // use R1 and R3.
keep if  EMPLOYMENT_YR1_v1 == 1   // KEEP FT in yr1 


table ( H1) (educ_group),  statistic(mean HRWG1) nototals nformat(%5.1f) 
collect title "Average Wages, FT Workers by H, ages 40-50, MEPS"
collect export "${out_tables}/Target_av_wages2.tex", tableonly replace
}


* Employment in MEPS - data to check later how well it matches CPS
qui{
clear
use "${data}/MEPS_merged_Health_Whites.dta"
set more off
keep if AGE_YR1<65 & AGE_YR1>24
keep if INSCOP_Y1==1 //& INSCOP_Y2==1  // use R1 and R3.
rename AGE_YR1 age

replace labor_force_Y1 = 2 if labor_force_Y1==3
replace labor_force_Y1 = 3 if labor_force_Y1==4
replace labor_force_Y1 = 4 if labor_force_Y1==5

rename labor_force_Y1 emp
drop if emp==.
 
collapse  (count) number=ID [aw=Sample_Weight], by(emp educ_group age)
reshape wide number, i(age educ_group) j(emp)
egen total=rowtotal(number1 number2 number3 number4)
replace number1=number1/total*100
replace number2=number2/total*100
replace number3=number3/total*100
replace number4=number4/total*100
sort  educ_group age
rename educ_group education
sort age education
save  "${data}\MEPS LS Ins.dta", replace
}


* Employment by health 
* save the data to plot later agaist model generated data. 
qui{
clear
use "${data}/MEPS_merged_Health_Whites.dta"
set more off
keep if AGE_YR1<65 & AGE_YR1>24
keep if INSCOP_Y1==1 //& INSCOP_Y2==1  // use R1 and R3.
rename AGE_YR1 age


rename EMPLOYMENT_YR1_v1 Employment // ==1 if FT
drop if Employment==.

keep H1   Employment   ID  age  educ_group du dp s
		rename educ_group education

collapse (mean) Employment , by(education age  H1) fast
drop if  Employment==.
drop if H1==.
drop if education==.
replace Employment=Employment*100

reshape wide Employment, i(age  education) j(H1)

reshape wide Employment*, i(age) j(education)
sort age
save  "${data}\Employment Distribution Health No Shocks.dta", replace
}



*   Table 81 in Appendix 
qui{
* first column in table 81
clear
use "${data}/MEPS_merged_Health_Whites.dta"
set more off
keep if AGE_YR1<65 & AGE_YR1>24
rename AGE3 age

keep if INSCOP_Y1==1 //& INSCOP_Y2==1  // use R1 and R3.
gen insurance=0 if  (labor_force3==2  |   labor_force3==4 )
replace  insurance=1 if  (labor_force3==3  |   labor_force3==5 )

keep if labor_force3 >=2 & labor_force3!=.
keep if labor_force1 >=2 & labor_force1!=.

tabstat HRWG3, stat(min max)
drop if HRWG3==.
drop if HRWG1==.

gen log_wage=log(HRWG3)
gen log_wages_lag = log(HRWG1)
gen age_sq=age^2
gen age_cub=age^3
tabstat log_wage , stat(mean sd)

//reg log_wage log_wages_lag age age_sq age_cub  i.year i.s2 i.dp2 i.du2 i.educ_group i.H3 

reg log_wage log_wages_lag age age_sq age_cub  i.year i.s i.dp i.du i.educ_group i.H1 
eststo
	esttab using "${out_tables}\wages.tex", varwidth(25) nogaps   compress label nobaselevels indicate("Year Dummies=*year*" "Education= *educ_group*"  "Health = *H1*"  "Age cubic = age age_sq age_cub")  replace  ///
	nodepvars nonumbers noconstant mtitles("1") star(* 0.1 ** 0.05 *** 0.01) ///
	se  order (log_wages_lag) b(3) 
	eststo clear
	

* second column of Table 81 in Appendix	
*** hours worked - part of same regressions table
** keep workers in R1. we are interested in the effects of shocks on hours. 
clear
use "${data}/MEPS_merged_Health_Whites.dta"
set more off
keep if AGE_YR1<65 & AGE_YR1>24
rename AGE3 age
gen age_sq=age^2
gen age_cub=age^3

keep if INSCOP_Y1==1 //& INSCOP_Y2==1  // use R1 and R3.
*keep if labor_force3 >=2 & labor_force3!=.
*keep if labor_force1 >=2 & labor_force1!=.

* HOURS
foreach num of numlist 1 2 3 4 5 {
replace HOUR`num'=0 if HOUR`num'==-1  // inapplicable because not employed
replace HOUR`num'=. if HOUR`num'<0 & HOUR`num'!=. // missing information on hours
replace HOUR`num'=70 if HOUR`num'>70 & HOUR`num'!=.
}


reg HOUR3  i.s i.dp i.du  HOUR1 age age_sq age_cub  i.year  i.educ_group i.H1 

tabstat HOUR3 , stat(mean sd)


* third column of Table 81 -- earnings  
clear
use "${data}/MEPS_merged_Health_Whites.dta"
set more off
keep if AGE_YR1<65 & AGE_YR1>24
rename AGE3 age
gen age_sq=age^2
gen age_cub=age^3

keep if INSCOP_Y1==1 & INSCOP_Y2==1  // use R1 and R3.

	
tabstat WAGE_Y2, stat(mean sd min p25 p50 p90 p99)
replace WAGE_Y2=1 if WAGE_Y2==0
replace WAGE_Y1=1 if WAGE_Y1==0

gen health_fair = 0 
replace health_fair=1 if H1==2
gen health_good = 0 
replace health_good=1 if H1==3

gen health_fair2 = 0 
replace health_fair2=1 if H3==2
gen health_good2 = 0 
replace health_good2=1 if H3==3


*gen HS=0
*replace HS=1 if EDUCTYPE ==2
gen Some_college=0
replace Some_college=1 if educ_group ==2
gen College=0
replace College=1 if educ_group ==3



tabstat WAGE_Y2 , stat(mean sd)
//boxcox WAGE_Y2   WAGE_Y1 , model(lambda) notrans(s dp du age age_sq age_cub  year   Some_college College health_fair health_good) lrtest 
// execute above command to get the coefficients for below, we need to change the 0's to 1's earlier in the code
gen WAGE_Y2_trans = ((WAGE_Y2^.3228158)-1)/.3228158
gen WAGE_Y1_trans = ((WAGE_Y1^.3228158)-1)/.3228158  
gen AGE_CUB_YR2 =AGE_YR2^3 


reg WAGE_Y2_trans   WAGE_Y1_trans s2 dp2 du2  AGE_YR2 AGE_SQ_YR2 AGE_CUB_YR2 i.year  Some_college College health_fair2 health_good2  

tabstat WAGE_Y2_trans , stat(mean sd)
}


* regression of earnings on shocks - we will compare model and data (save data to consturct table 69 in Appendix) 
qui{
clear
use "${data}/MEPS_merged_Health_Whites.dta"
set more off
keep if AGE_YR2<65 & AGE_YR2>24
rename AGE3 age
keep if  INSCOP_Y2==1  // use R1 and R3.

drop if educ_group==.
drop if H3==.
gen AGE_CUB_YR2 =AGE_YR2^3 

gen EMPLOYED1 = 0 if EMP1==0 & EMP2==0
replace EMPLOYED1 = 1 if EMP1==1 | EMP2==1 |  EMP1==2 | EMP2==2

rename WAGE_Y2 earnings_noise
	rename s2 s_record
	rename du2 du_record
	rename dp2 dp_record
	rename educ_group education
	rename H3 H
	rename H1 H_lag
	drop age 
	rename AGE_YR2 age
	rename AGE_SQ_YR2 age_sq 
	rename AGE_CUB_YR2 age_cub
	rename type2_2 type2_recorded
	rename EMP_Y1 EMP_lag
	
	gen I_poor = 0 
	replace I_poor=1 if H==1
	
	reg earnings_noise   s_record dp_record du_record  age age_sq age_cub  i.year i.education i.H  


	gen model_data=0
keep 	model_data earnings_noise  s_record du_record dp_record education age age_sq age_cub year ID H H_lag type2_recorded EMP_lag
save  "${data}\Earnings_regression.dta", replace 
}



* AVERAGE WAGES BY AGE and EDUCATION and VARIANCE OF WAGES -- keep only FT in Good H - For Figure 24
qui{
clear
use "${data}/MEPS_merged_Health_Whites.dta"
set more off
keep if AGE_YR1<65 & AGE_YR1>24
keep if H1==3
keep if INSCOP_Y1==1  // use R1 and R3.
keep if  EMPLOYMENT_YR1_v1 == 1   // KEEP FT in yr1 

drop if HRWG1==.

gen log_wage=log(HRWG1)
		rename educ_group  education
collapse (sd) var_wages=log_wage (mean) wages=HRWG1 , by(education AGE1)
replace var_wages=var_wages^2
rename AGE1 age
	reshape wide wages* var_wages, i(age ) j(education)
	sort age
save  "${data}\Wages Good H.dta", replace
}





